﻿/*
 * ========================================================================
 * Copyright(c) 2008-2010北京高阳金信信息技术有限公司, All Rights Reserved.
 * ========================================================================
 *  通用分类管理器组件，支持无限分类
 * 
 * 作者：邓太华     时间：2009-12-15
 * 版本：V1.0
 * 
 * 修改者：         时间：                
 * 修改说明：
 * ========================================================================
*/

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using PWMIS.DataProvider.Adapter;
using System.Data;

namespace Hisuntech.SystemFramework.Common
{
    /// <summary>
    /// 分类管理器 ver1.0 by dth 2009.12.15，支持无限分类
    /// </summary>
    public class CategoryManager
    {
        #region 属性
        private string _tableName;
        /// <summary>
        /// 分类信息表表名称
        /// </summary>
        public string TableName
        {
            get { return _tableName; }
            set { _tableName = value; }
        }

        private string _errMessage;
        /// <summary>
        /// 错误信息
        /// </summary>
        public string ErrMessage
        {
            get { return _errMessage; }
            private set { _errMessage = value; }
        }
        #endregion

        /// <summary>
        /// 默认构造函数
        /// </summary>
        public CategoryManager()
        {
            this.TableName = "Tb_Sys_Category";
            checkTable();
        }

        /// <summary>
        /// 使用指定的表名称初始化构造函数
        /// </summary>
        /// <param name="tableName"></param>
        public CategoryManager(string tableName)
        {
            this.TableName = tableName;
            checkTable();
        }

        #region 初始化
        /// <summary>
        /// 检查表是否存在，如果不存在则创建一个
        /// </summary>
        private void checkTable()
        {
            object result = MyDB.Instance.ExecuteScalar("select count(*) from " + this.TableName);
            if (result == null)
                CreateTable(this.TableName);
        }

        /// <summary>
        /// 创建分类表
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        private bool CreateTable(string tableName)
        {
            string sql = @"
Create Table %TableName%
(
  [ID] [integer] primary key not null,
  [Name] [varchar](50) not null,
  [ParentId] [int] not null,
  [Description] [varchar](250),
  [OrderId] [int],
  [AtTime] [datetime]
)
";
            sql = sql.Replace("%TableName%", tableName);


           int count= MyDB.Instance.ExecuteNonQuery(sql);
            return MyDB.Instance.ErrorMessage == "";

        }
        #endregion

        #region 实体数据操作

        /// <summary>
        /// 获取当前数据表中的最大标示号（用于插入数据）
        /// </summary>
        /// <returns></returns>
        private int getMaxId()
        {
            //注：Oracle 不支持在字段名称前加 ，但是支持在 大写 字段/表 名称上加双引号 号，例如下面的语句合法
            //select NO,"SYSNAM" from fn00f t;
            //select NO,"SYSNAM" from "FN00F" t;
            //select NO,sysnam from "FN00F" t;

            object result=MyDB.Instance.ExecuteScalar("select Max([ID]) from ["+this.TableName+"]");
            return result==DBNull.Value ?0:(int)result;
        }

        /// <summary>
        /// 更新一条分类信息
        /// </summary>
        /// <param name="info"></param>
        /// <returns></returns>
        public bool Update(CategoryInfo info)
        {
            this.ErrMessage = "";
            string sql = "update [" + this.TableName + "] set [Name]=@Name,[ParentId]=@ParentId,[Description]=@Description,[OrderId]=@OrderId,[AtTime]=@AtTime  where [ID]=@ID";
            IDataParameter[] paras ={
                                   MyDB.Instance.GetParameter ("@Name",info.Name),
                                   MyDB.Instance.GetParameter ("@ParentId",info.ParentId ),
                                   MyDB.Instance.GetParameter ("@Description",info.Description),
                                   MyDB.Instance.GetParameter ("@OrderId",info.OrderId),
                                   MyDB.Instance.GetParameter ("@AtTime",DateTime.Now ),
                                   MyDB.Instance.GetParameter ("@ID",info.Id ),
                                   };
            int count = MyDB.Instance.ExecuteNonQuery(sql, CommandType.Text, paras);
            this.ErrMessage=MyDB.Instance.ErrorMessage;
            return count > 0 ? true : false;

           
        }

        /// <summary>
        /// 使用事务更新一批分类信息
        /// </summary>
        /// <param name="infos"></param>
        /// <returns></returns>
        public bool Update(List<CategoryInfo> infos)
        {
            this.ErrMessage = "";
            PWMIS.DataProvider.Data.CommonDB db = MyDB.GetDBHelper();
            db.OnErrorThrow = true;//出错抛出异常

            try
            {
                string sql = "update [" + this.TableName + "] set [Name]=@Name,[ParentId]=@ParentId,[Description]=@Description,[OrderId]=@OrderId,[AtTime]=@AtTime  where [ID]=@ID";
                IDataParameter[] paras ={
                                   db.GetParameter("@ID",DbType.Int32), 
                                   db.GetParameter("@Name",DbType.AnsiString,50), 
                                   db.GetParameter("@ParentId",DbType.Int32), 
                                   db.GetParameter("@Description",DbType.AnsiString,250), 
                                   db.GetParameter("@OrderId",DbType.Int32), 
                                   db.GetParameter("@AtTime",DbType.DateTime ), 
                                   };
                //bool flag = true ;
                db.BeginTransaction();
                foreach (CategoryInfo info in infos)
                {
                    paras[0].Value = info.Id;
                    paras[1].Value = info.Name;
                    paras[2].Value = info.ParentId;
                    paras[3].Value = info.Description;
                    paras[4].Value = info.OrderId;
                    paras[5].Value = DateTime.Now;
                    db.ExecuteNonQuery(sql, CommandType.Text, paras);

                    //if (db.ExecuteNonQuery(sql, CommandType.Text, paras) < 0)
                    //{
                    //    //出现了错误，已经自动回滚
                    //    flag = false;
                    //    break;
                    //}
                }
                //if(flag )
                db.Commit();
            }
            catch (System.Exception ex)
            {
                this.ErrMessage = ex.Message;
                db.Rollback();
                return false;
            }
            return true;
        }

        /// <summary>
        /// 删除一个分类
        /// </summary>
        /// <param name="info"></param>
        /// <returns></returns>
        public bool Remove(CategoryInfo info)
        {
            return Remove (info.Id );
        }

        /// <summary>
        /// 删除一个分类
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool Remove(int id)
        {
            //先获取所有子节点信息
            List<CategoryInfo> allInfo = this.GetChildTree(id);
            
            string sql = "delete from ["+ this.TableName +"] where id="+id;
            int count = MyDB.Instance.ExecuteNonQuery(sql);

            string idList = "";
            foreach (CategoryInfo info in allInfo)
            {
                idList += "," + info.Id;
            }
            if (idList != "")
            {
                sql = "delete from [" + this.TableName + "] where id in (" + idList.TrimStart(',') + ")";
                count += MyDB.Instance.ExecuteNonQuery(sql);
            }
            
            return count > 0 ? true : false;
        }

        private void getChilds(List<CategoryInfo> brothers,List<CategoryInfo> all)
        {
            foreach (CategoryInfo info in brothers)
            {
                all.Add(info);
                List<CategoryInfo> childs = this.GetList(info.Id );
                getChilds(childs,all);
            }
        }

        /// <summary>
        /// 获取当前节点下面所有的子节点树形列表
        /// </summary>
        /// <param name="currID"></param>
        /// <returns></returns>
        public List<CategoryInfo> GetChildTree(int currID)
        {
            List<CategoryInfo> allInfo = new List<CategoryInfo>();
            getChilds(this.GetList(currID), allInfo);
            return allInfo;
        }

        

        /// <summary>
        /// 增加一个分类
        /// </summary>
        /// <param name="info"></param>
        /// <returns></returns>
        public bool Add(CategoryInfo info)
        {
            this.ErrMessage = "";
            info.Id=this.getMaxId ()+1;
            string sql = "insert into [" + this.TableName + "] ([ID],[Name],[ParentId],[Description],[OrderId],[AtTime] ) values(@ID,@Name,@ParentId,@Description,@OrderId,@AtTime)";
            IDataParameter[] paras ={
                                   MyDB.Instance.GetParameter ("@ID",info.Id),
                                   MyDB.Instance.GetParameter ("@Name",info.Name),
                                   MyDB.Instance.GetParameter ("@ParentId",info.ParentId ),
                                   MyDB.Instance.GetParameter ("@Description",info.Description),
                                   MyDB.Instance.GetParameter ("@OrderId",info.OrderId),
                                   MyDB.Instance.GetParameter ("@AtTime",DateTime.Now )
                                   };
            int count = MyDB.Instance.ExecuteNonQuery(sql, CommandType.Text, paras);
            return count>0?true:false;
        }

        /// <summary>
        /// 添加一批分类
        /// </summary>
        /// <param name="infos"></param>
        /// <returns></returns>
        public bool Add(List<CategoryInfo> infos)
        {
            this.ErrMessage = "";
            PWMIS.DataProvider.Data.CommonDB db = MyDB.GetDBHelper();
            db.OnErrorThrow = true;//出错抛出异常
            
            try
            {
                string sql = "insert into [" + this.TableName + "] ([ID],[Name],[ParentId],[Description],[OrderId],[AtTime] ) values(@ID,@Name,@ParentId,@Description,@OrderId,@AtTime)";
                IDataParameter[] paras ={
                                   db.GetParameter("@ID",DbType.Int32), 
                                   db.GetParameter("@Name",DbType.AnsiString,50), 
                                   db.GetParameter("@ParentId",DbType.Int32), 
                                   db.GetParameter("@Description",DbType.AnsiString,250), 
                                   db.GetParameter("@OrderId",DbType.Int32), 
                                   db.GetParameter("@AtTime",DbType.DateTime ), 
                                   };
                //bool flag = true ;
                db.BeginTransaction();
                foreach (CategoryInfo info in infos)
                {
                    paras[0].Value = this.getMaxId();
                    paras[1].Value = info.Name;
                    paras[2].Value = info.ParentId ;
                    paras[3].Value = info.Description;
                    paras[4].Value = info.OrderId;
                    paras[5].Value = DateTime.Now;
                    db.ExecuteNonQuery(sql, CommandType.Text, paras);

                    //if (db.ExecuteNonQuery(sql, CommandType.Text, paras) < 0)
                    //{
                    //    //出现了错误，已经自动回滚
                    //    flag = false;
                    //    break;
                    //}
                }
                //if(flag )
                db.Commit();
            }
            catch (System.Exception ex)
            {
                this.ErrMessage = ex.Message;
                db.Rollback();
                return false;
            }
            return true;
        }

        /// <summary>
        /// 获取符合父类的列表
        /// </summary>
        /// <param name="parentId"></param>
        /// <returns></returns>
        public List<CategoryInfo> GetList(int parentId)
        {
            string sql = "select [ID],[Name],[ParentId],[Description],[OrderId],[AtTime] from [" + this.TableName + "] where [ParentId]=" + parentId + " order by [OrderId],[AtTime]";
            return GetListBySql(sql);
        }

        /// <summary>
        /// 获取所以记录
        /// </summary>
        /// <returns></returns>
        public List<CategoryInfo> GetAll()
        {
            string sql = "select [ID],[Name],[ParentId],[Description],[OrderId],[AtTime] from [" + this.TableName + "] order by [ParentId]";
            return GetListBySql(sql);
        }

        private List<CategoryInfo> GetListBySql(string sql)
        {
            PWMIS.DataProvider.Data.CommonDB db = MyDB.GetDBHelper();
            IDataReader reader = db.ExecuteDataReader(sql);
            if (reader != null)
            {
                List<CategoryInfo> list = new List<CategoryInfo>();
                while (reader.Read())
                {
                    CategoryInfo info = new CategoryInfo();
                    info.Id = Convert.ToInt32(reader[0]);
                    info.Name = Convert.ToString(reader[1]);
                    info.ParentId = Convert.ToInt32(reader[2]);
                    info.Description = Convert.ToString(reader[3]);
                    info.OrderId = Convert.ToInt32(reader[4]);

                    list.Add(info);

                }
                reader.Close();
                return list;
            }
            return null;
        }

        /// <summary>
        /// 获取一个分类
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public CategoryInfo GetOne(int id)
        {
            string sql = "select [ID],[Name],[ParentId],[Description],[OrderId],[AtTime] from [" + this.TableName + "] where [ID]=" + id; ;
            List<CategoryInfo> list= GetListBySql(sql);
            return list != null && list.Count > 0 ? list[0] : null;
        }

        #endregion

        #region 业务逻辑

        /// <summary>
        /// 创建一个子分类（并保存到数据库）
        /// </summary>
        /// <param name="currID">当前节点ID</param>
        /// <param name="name">新节点名称</param>
        /// <param name="description">新节点描述</param>
        /// <returns>子节点</returns>
        public CategoryInfo CreateChild(int currID, string name, string description)
        {
            CategoryInfo info = new CategoryInfo();
            info.ParentId = currID;
            info.Name = name;
            info.Description = description;
            object result = MyDB.Instance.ExecuteScalar("select Max([OrderId]) from [" + this.TableName + "] where [ParentId]=" + currID);
            int maxId = result == DBNull.Value ? 0 : (int)result;
            info.OrderId = (++maxId);
            if (Add(info))
                return info;
            else
                return null;
        }

        /// <summary>
        /// 将当前节点向上移动（并保存到数据库）
        /// </summary>
        /// <param name="currInfo">当前节点对象</param>
        /// <param name="errMessage">错误信息</param>
        /// <returns>是否成功</returns>
        public bool MoveUp(CategoryInfo currInfo, out string errMessage)
        {
            errMessage = "";
            List<CategoryInfo> brothers = this.GetList(currInfo.ParentId);
            if (brothers == null || brothers.Count < 2)
            {
                errMessage = "无其它同级节点！";
                return false;
            }
            if (brothers[0].Id == currInfo.Id)
            {
                errMessage = "已经是本类第一个节点！";
                return false;
            }

            for (int i = brothers.Count - 1; i >= 0; i--)
            {
                if (brothers[i].OrderId <= currInfo.OrderId && brothers[i].Id != currInfo.Id)
                {
                    //找到比当前小的，交换
                    int temp = brothers[i].OrderId;
                    brothers[i].OrderId = currInfo.OrderId;
                    currInfo.OrderId = temp;

                    bool flag = Update(new List<CategoryInfo>() { brothers[i], currInfo });
                    if (!flag)
                        errMessage = this.ErrMessage;
                    return flag;
                }
            }
            errMessage = "未找到上一节点！";
            return false;

        }

        /// <summary>
        /// 将当前节点 向下 移动（并保存到数据库）
        /// </summary>
        /// <param name="currInfo">当前节点对象</param>
        /// <param name="errMessage">错误信息</param>
        /// <returns>是否成功</returns>
        public bool MoveDown(CategoryInfo currInfo, out string errMessage)
        {
            errMessage = "";
            List<CategoryInfo> brothers = this.GetList(currInfo.ParentId);
            if (brothers == null || brothers.Count < 2)
            {
                errMessage = "无其它同级节点！";
                return false;
            }
            if (brothers[brothers.Count - 1].Id == currInfo.Id)
            {
                errMessage = "已经是本类最后一个节点！";
                return false;
            }

            for (int i = 0; i < brothers.Count; i++)
            {
                if (brothers[i].OrderId >= currInfo.OrderId && brothers[i].Id != currInfo.Id)
                {
                    //找到比当前大的，交换
                    int temp = brothers[i].OrderId;
                    brothers[i].OrderId = currInfo.OrderId;
                    currInfo.OrderId = temp;

                    bool flag = Update(new List<CategoryInfo>() { brothers[i], currInfo });
                    if (!flag)
                        errMessage = this.ErrMessage;
                    return flag;
                }
            }
            errMessage = "未找到上一节点！";
            return false;

        }

        /// <summary>
        /// 向左移动（提升当前节点到它的父节点所在的级别）
        /// </summary>
        /// <param name="currInfo">当前节点</param>
        /// <param name="errMessage"></param>
        /// <returns></returns>
        public bool MoveLeft(CategoryInfo currInfo, out string errMessage)
        {
            errMessage = "";
            CategoryInfo parentInfo = this.GetOne(currInfo.ParentId);
            if (parentInfo != null)
            {
                currInfo.ParentId = parentInfo.ParentId;
                currInfo.OrderId = (++parentInfo.OrderId);
                bool flag = Update(currInfo);
                if (!flag)
                    errMessage = "移动失败";
                return flag;
            }
            else
            {
                errMessage = "无法找到父节点！";
                return false;
            }
        }

        /// <summary>
        /// 向左移动（把当前节点降级到它的上一个兄弟节点下面，成为原兄弟节点的子节点）
        /// </summary>
        /// <param name="currInfo">当前节点对象</param>
        /// <param name="errMessage">错误信息</param>
        /// <returns>是否成功</returns>
        public bool MoveRight(CategoryInfo currInfo, out string errMessage)
        {
            errMessage = "";
            List<CategoryInfo> brothers = this.GetList(currInfo.ParentId);
            if (brothers == null)
            {
                errMessage = "无法找到兄弟节点！";
                return false;
            }
            else if (brothers.Count == 1)
            {
                errMessage = "无法找到其它兄弟节点！";
                return false;
            }
            else
            {
                for (int i = brothers.Count - 1; i >= 0; i--)
                {
                    if (brothers[i].OrderId <= currInfo.OrderId && brothers[i].Id!=currInfo.Id)
                    {
                        //找到上一个兄弟节点 brothers[i]
                        List<CategoryInfo> childs = this.GetList(brothers[i].Id);
                        int newOrderId = childs == null || childs.Count == 0 ? 1 : childs[childs.Count - 1].OrderId + 1;

                        currInfo.ParentId = brothers[i].Id;//修改父节点，降级
                        currInfo.OrderId = newOrderId;
                        bool flag=this.Update(currInfo);
                        if (!flag)
                            errMessage = this.ErrMessage;
                        return flag;
                    }
                }
            }
            errMessage = "同级节点顺序号错误，未找到上一兄弟节点！";
            return false;
        }

        /// <summary>
        /// 自动保存节点
        /// </summary>
        /// <param name="info"></param>
        /// <returns></returns>
        public bool Save(CategoryInfo info)
        {
            if (info.Id == 0)
                return Add(info);
            else
                return Update(info);
        }

        #endregion

        #region 实体类

        /// <summary>
        /// 分类信息
        /// </summary>
        public class CategoryInfo
        {
            private int _id;
            /// <summary>
            /// 唯一标示
            /// </summary>
            public int Id
            {
                get { return _id; }
                set { _id = value; }
            }
            private string _name;
            /// <summary>
            /// 分类名称
            /// </summary>
            public string Name
            {
                get { return _name; }
                set { _name = value; }
            }
            private int _parentId;
            /// <summary>
            /// 父标示
            /// </summary>
            public int ParentId
            {
                get { return _parentId; }
                set { _parentId = value; }
            }
            private string _description;
            /// <summary>
            /// 描述
            /// </summary>
            public string Description
            {
                get { return _description; }
                set { _description = value; }
            }
            private int _orderId;
            //序号
            public int OrderId
            {
                get { return _orderId; }
                set { _orderId = value; }
            }
        }

        #endregion
    }
}
